SQL CREATE Table

    After creating and selecting a database using CREATE and USE commands now we can create a table inside that database, and keep remember we can create more than one table in a single database.

    What is a Table in the Database?

    A table is a collection of rows and columns which form cells like structure and the data reside in these cells, and each data has a relation with its corresponding row and column.

    CREATE TABLE commands

    With the help of CREATE TABLE command, we can create a new table in a database and make sure while you creating a table you must have selected a specific database. Syntax 

    Follow this Syntax to CREATE a table:
CREATE TABLE table_name (
column_name_1 datatype,
column_name_2 datatype,
……
column_name_n datatype)

    Example

    First, select the database: 

    USE student_db;

    Output 

    Database Changed;

    Create Table Query 

    CREATE TABLE students (
    id INT(6),
    name VARCHAR(30),
    age INT(4),
    grades VARCHAR(2),
    marks INT(4)
    );

    Output 

    Query OK, 0 rows affected (0.50 sec)

    Verify If the table is created: Query 

    SHOW TABLES;

    Output 

    +----------------------+
| Tables_in_student_db |
+----------------------+
| students             |
+----------------------+
1 rows in set (0.00 sec)

    To verify if your table has been created successfully along with its attributes, use the DESC SQL command. Query 

    DESC students;

    Output 

    +--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(6)      | YES  |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| age    | int(4)      | YES  |     | NULL    |       |
| grades | varchar(2)  | YES  |     | NULL    |       |
| marks  | int(4)      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.14 sec)

    Create a Table using Another table:

    In a single database, we can have multiple tables, and all the tables can be created using the CREATE TABLE command. With the help of CREATE table command, we can copy an existing table and create a new one, as a backup table.

    Syntax 

    CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name;

    Example

    Create a duplicate table Query 

    CREATE TABLE student_backup as SELECT * FROM students;

    Output 

    Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

    Verify Query 

    SHOW TABLES;

    Output 

    +----------------------+
| Tables_in_student_db |
+----------------------+
| student_backup       |
| students             |
+----------------------+
2 rows in set (0.00 sec)

    Summary

    • We can create multiple tables inside a database.
    • A table is a collection of rows and columns, which form cells.
    • Data reside in the cells.
    • Each data has a relation with its corresponding row and column.
    • To create a table, we use CREATE TABLE command.

    People are also reading:

    Previous Tutorial Next Tutorial